package com.gsit.expenditure.services;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.gsit.expenditure.domain.Invoice;

@Repository("invoiceService")
public class InvoiceService {

	@PersistenceContext
	private EntityManager entityManager;

	@Transactional
	public List<Invoice> findAll() {
		return entityManager.createNamedQuery("Invoice.findAll", Invoice.class).getResultList();
	}

	@Transactional
	public <T> T find(Class<T> entityClass, Long id) {
		return entityManager.find(entityClass, id);
	}

	@Transactional
	public Invoice saveOrUpdate(Invoice invoice) { 
		return entityManager.merge(invoice);
	}
	
	
	@Transactional
	public List<Invoice> selectAllInvoice() {
		return entityManager.createQuery("select invoice from Invoice invoice").getResultList();
	}
	

	@Transactional
	public void delete(Invoice invoice) {
		entityManager.remove(entityManager.merge(invoice));
	}
	

	@Transactional
	public Invoice findByReferenceNo(String referenceNumber) {
		Query query = entityManager.createQuery("SELECT invoice FROM Invoice invoice where invoiceNumber like '%"  + referenceNumber + "%'");
		
		return (Invoice) query.getSingleResult();

	}
	
	@Transactional
	public List<Invoice> findByInvoiceNumber(String invoiceNumber) {
		Query query = entityManager.createQuery("SELECT invoiceNumber FROM Invoice Invoice where invoiceNumber like '%"  + invoiceNumber + "%'");
		List<Invoice> customersList = query.getResultList();
		return customersList;

	}
	
	@Transactional
	public List<Invoice> findInvoiceBetweenDates(String fromDate , String toDate) {
		Query query = entityManager.createQuery("select invoice from Invoice  invoice where  invoice.createdDate  between  DATE('"+fromDate+"')  AND   DATE('"+toDate+"') " );
		return query.getResultList();
	}
	
	@Transactional
	public List<Invoice> findInvoices(Invoice invoice , String fromDate, String toDate, String customerNameOrId, String vehicleNameOrId) {

		StringBuilder sqlQueryBuilder = new StringBuilder("select invoice from Invoice invoice left outer join fetch invoice.bills as bills " +
				                                           "left outer join fetch bills.customer customer " +
				                                           "left outer join fetch bills.vehicle vehicle where ");
		
		sqlQueryBuilder.append(" invoice.status = '"+invoice.getStatus() +"' ");
		
		if(StringUtils.isNotBlank(customerNameOrId)){
			sqlQueryBuilder.append(" and customer.firstName like '%" + customerNameOrId.trim() + "%'  or customer.id = "+customerNameOrId.trim() );
		}
		
		if(StringUtils.isNotBlank(vehicleNameOrId)){
			sqlQueryBuilder.append(" and vehicle.vehicleNumber like '%" + vehicleNameOrId.trim() + "%' or vehicle.id ="+vehicleNameOrId.trim());
		}
		 
		if(StringUtils.isNotBlank(invoice.getInvoiceNumber())){
			sqlQueryBuilder.append(" and invoice.invoiceNumber like '%" + invoice.getInvoiceNumber().trim() + "%'" );
		}
		
		if(StringUtils.isNotBlank(fromDate)  && StringUtils.isNotBlank(toDate)){
			sqlQueryBuilder.append(" and invoice.createdDate  between  DATE('"+fromDate+"')  AND   DATE('"+toDate+"')");
		}
		
        sqlQueryBuilder.append(" order by invoice.createdDate ASC") ;
        
        System.out.println(" sqlQueryBuilder :: "+sqlQueryBuilder);
        
		Query query = entityManager.createQuery(sqlQueryBuilder.toString() );
		return query.getResultList();
	}
	
	
		

}
